# coding=utf-8
#!/usr/bin/python3
from openpyxl import load_workbook

# 文件名默认是: openpyxl.xlsx , 要对比的文件不能在其他软件(例如wps,office)打开, 原始sheet要修改为Sheet1, 新生成的sheet为newsheet,
# 两部分内容的key需分别在 A 列和 F 列, key忽略了大小写和下划线, 左右对比内容各支持 5 列, 一共10列.

# 文件名
addr = "openpyxl.xlsx"
# 打开文件
wb = load_workbook(addr)
newSheet = wb.create_sheet("newsheet")
ws = wb["Sheet1"]
AListSize = max((bb.row for bb in ws['A'] if bb.value))
DListSize = max((bb.row for bb in ws['F'] if bb.value))

def convert(aa):
    return str(aa.value).lower().strip().replace('_', '').replace(' ', '')

dicABC = {}
dicDEF = {}

for indexi, i in enumerate(ws.rows):
    alist = []
    alist.append(i[0])
    alist.append(i[1])
    alist.append(i[2])
    alist.append(i[3])
    alist.append(i[4])
    dicABC[convert(i[0])] = alist
    blist = []
    blist.append(i[5])
    blist.append(i[6])
    blist.append(i[7])
    blist.append(i[8])
    blist.append(i[9])
    dicDEF[convert(i[5])] = blist

#  查找,对比
indexCount = 0
for k, v in dicABC.items():
    if dicDEF.get(k):
        kList = dicDEF.get(k)
        list_ = kList[0]
        newSheet.cell(row=indexCount + 1, column=6).value = kList[0].value
        newSheet.cell(row=indexCount + 1, column=7).value = kList[1].value
        newSheet.cell(row=indexCount + 1, column=8).value = kList[2].value
        newSheet.cell(row=indexCount + 1, column=9).value = kList[3].value
        newSheet.cell(row=indexCount + 1, column=10).value = kList[4].value
        dicDEF.pop(k)
    else:
        newSheet.cell(row=indexCount + 1, column=6).value = ''
        newSheet.cell(row=indexCount + 1, column=7).value = ''
        newSheet.cell(row=indexCount + 1, column=8).value = ''
        newSheet.cell(row=indexCount + 1, column=9).value = ''
        newSheet.cell(row=indexCount + 1, column=10).value = ''
    indexCount = indexCount + 1

# 剩余的-------------
index = 0
for k, v in dicABC.items():
    newSheet.cell(row=index + 1, column=1).value = v[0].value
    newSheet.cell(row=index + 1, column=2).value = v[1].value
    newSheet.cell(row=index + 1, column=3).value = v[2].value
    newSheet.cell(row=index + 1, column=4).value = v[3].value
    newSheet.cell(row=index + 1, column=5).value = v[4].value
    index = index + 1

index = 0
for k, v in dicDEF.items():
    newSheet.cell(row=DListSize + index + 1, column=6).value = v[0].value
    newSheet.cell(row=DListSize + index + 1, column=7).value = v[1].value
    newSheet.cell(row=DListSize + index + 1, column=8).value = v[2].value
    newSheet.cell(row=DListSize + index + 1, column=9).value = v[3].value
    newSheet.cell(row=DListSize + index + 1, column=10).value = v[4].value
    index = index + 1

wb.save(addr)
print('---------结束了')
